import express from 'express';
import sqlite3 from 'sqlite3';

const router = express.Router();
const db = new sqlite3.Database('./data/mirna.db');

// 获取所有检测盒
router.get('/boxes', (req, res) => {
  const sql = `SELECT * FROM test_boxes ORDER BY created_at DESC`;
  
  db.all(sql, [], (err, rows) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    res.json({ boxes: rows });
  });
});

// 更新检测盒信息
router.put('/boxes/:boxId', (req, res) => {
  const { boxId } = req.params;
  const { batchNumber, firmwareVersion, status } = req.body;
  
  const sql = `UPDATE test_boxes SET batch_number = ?, firmware_version = ?, status = ? WHERE box_id = ?`;
  
  db.run(sql, [batchNumber, firmwareVersion, status, boxId], function(err) {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    
    if (this.changes === 0) {
      return res.status(404).json({ error: '检测盒不存在' });
    }
    
    res.json({ success: true, message: '检测盒信息已更新' });
  });
});

// 获取所有检测结果
router.get('/results', (req, res) => {
  const sql = `SELECT tr.*, tb.batch_number 
               FROM test_results tr 
               JOIN test_boxes tb ON tr.box_id = tb.box_id 
               ORDER BY tr.created_at DESC`;
  
  db.all(sql, [], (err, rows) => {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    res.json({ results: rows });
  });
});

// 删除检测结果
router.delete('/results/:id', (req, res) => {
  const { id } = req.params;
  
  const sql = `DELETE FROM test_results WHERE id = ?`;
  
  db.run(sql, [id], function(err) {
    if (err) {
      return res.status(500).json({ error: err.message });
    }
    
    if (this.changes === 0) {
      return res.status(404).json({ error: '结果不存在' });
    }
    
    res.json({ success: true, message: '检测结果已删除' });
  });
});

// 获取统计数据
router.get('/stats', (req, res) => {
  const stats = {};
  
  const queries = [
    {
      name: 'totalBoxes',
      sql: 'SELECT COUNT(*) as count FROM test_boxes'
    },
    {
      name: 'totalTests',
      sql: 'SELECT COUNT(*) as count FROM test_results'
    },
    {
      name: 'todayTests',
      sql: `SELECT COUNT(*) as count FROM test_results WHERE DATE(created_at) = DATE('now')`
    },
    {
      name: 'avgConfidence',
      sql: 'SELECT AVG(confidence_score) as avg FROM test_results WHERE confidence_score IS NOT NULL'
    }
  ];
  
  let completed = 0;
  queries.forEach(({ name, sql }) => {
    db.get(sql, [], (err, row) => {
      if (!err) {
        stats[name] = row.count || row.avg || 0;
      }
      if (++completed === queries.length) {
        res.json({ stats });
      }
    });
  });
});

export default router;